package com.studentportalen;


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "Portal";
	private static final int DATABASE_VERSION = 4;

	
	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		//creates a writable database for checking if database needs to be updated
		SQLiteDatabase dbw = getWritableDatabase();
		dbw.close();
	}
	
	private void exec(String query) {
		SQLiteDatabase dbw = getWritableDatabase();
		dbw.execSQL(query);
	}

	public Cursor select(String query) {
		SQLiteDatabase dbr = getReadableDatabase();

		Cursor s = dbr.rawQuery(query, null);
		return s;
	}

	public void closeDB() {
		this.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
		//Database creation 
		db.execSQL("drop table if exists student");
		db.execSQL("CREATE TABLE student (id integer primary key, name VARCHAR(50), password VARCHAR(50))");
		db.execSQL("INSERT INTO student (id,name,password) VALUES (870312,'Kurt Olsson','hej')");
		
		db.execSQL("drop table if exists course");
		db.execSQL("CREATE TABLE course (id integer primary key, name VARCHAR(50), homepage VARCHAR(500), prerequisites VARCHAR(500), purpose VARCHAR(500), date VARCHAR(50), location VARCHAR(50), scheduleId INTEGER)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (123456,'Database'" +
				",'http://www.cse.chalmers.se/edu/year/2011/course/TDA357/HT2011/'" +
				",'Programming experience (in Java) is a prerequisite, as is basic mathematical maturity, and a course in data structures. Dispensations can only be permitted with the permission of the course instructor. '" +
				",'Databases are a ubiquitous feature of the information technology society, and most people have come in contact with them, either directly while using the world wide web, or indirectly through banks, the tax office, etc. A course on database systems is therefore essential to a well-rounded education in computer science. '" +
				", '12/12-10','V-Huset'" +
				",201967)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (223456,'Project Managment'" +
				",'https://www.student.chalmers.se/hp/hp?hp_id=8398' " +
				",'While not being a formal prerequisite, this course is designed under the assumption that participating students hold a major in software engineering, information technology, or computer science.' " +
				",'The course aims to impart a declarative knowledge as well as a reflective understanding of project work, i.e. the organisation leadership and control of project activity, in order to prepare the student for participating in and improving the student’s ability to improve the performance of project-related work.' " +
				", '15/12-10','A-Huset'" +
				",202049)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (323456,'Mobile Computing'" +
				",'http://www.ixdcth.se/courses/2011/ciu195/' " +
				",'To be eligible for the course the student must have a Bachelor degree of 180 credits. Special requirements are TDA545 Object-oriented programming, 7.5 credits, and at least one of the courses TDA289 Human - computer interaction, TDA491 Graphical interfaces, DAT215 Design and implementation of graphical interfaces or equivalent. ' " +
				",'After  the  course  the  students  should  be  able  to  take  an  idea  and  turn  it  into  a  finished application, or an app as it is usually called.  This  includes  both  the  design  of  the  graphical  interface  and  the  interaction,  as  well as  the  implementation  of  the  design. ' " +
				", '17/12-10','V-Huset'" +
				",201015)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (423456,'Calculus'" +
				",'http://www.chalmers.se/math/SV/utbildning/grundutbildning-chalmers/mastersutbildning/engineering-mathematics/mve160' " +
				",'Basics in linear algebra, analysis in several variables, and programming' " +
				",'The student will be able to model some real world problems from science and technology, using mathematical tools from earlier courses as well as some new ideas and methods.'" +
				", '10/11-10','V-Huset'" +
				",201689)");
		
		db.execSQL("drop table if exists complete");
		db.execSQL("create table complete(s_id references student (id), c_id references course (id), grade VARCHAR(2), date VARCHAR(50), primary key (s_id, c_id))");
		db.execSQL("INSERT INTO complete (s_id,c_id,grade,date) VALUES (870312,123456,'4','10/3-11')");
		db.execSQL("INSERT INTO complete (s_id,c_id,grade,date) VALUES (870312,223456,'3','12/4-10')");
		
		db.execSQL("drop table if exists registerd");
		db.execSQL("create table registerd(s_id references student (id), c_id references course (id), primary key (s_id, c_id))");
		db.execSQL("INSERT INTO registerd (s_id,c_id) VALUES (870312,123456)");
		db.execSQL("INSERT INTO registerd (s_id,c_id) VALUES (870312,323456)");
		
		db.execSQL("drop table if exists examreg");
		db.execSQL("create table examreg(s_id references student (id), c_id references course (id), code INTEGER,  primary key (s_id, c_id))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//Database creation 
		db.execSQL("drop table if exists student");
		db.execSQL("CREATE TABLE student (id integer primary key, name VARCHAR(50), password VARCHAR(50))");
		db.execSQL("INSERT INTO student (id,name,password) VALUES (870312,'Kurt Olsson','hej')");
				
		db.execSQL("drop table if exists course");
		db.execSQL("CREATE TABLE course (id integer primary key, name VARCHAR(50), homepage VARCHAR(500), prerequisites VARCHAR(500), purpose VARCHAR(500), date VARCHAR(50), location VARCHAR(50), scheduleId INTEGER)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (123456,'Database'" +
				",'http://www.cse.chalmers.se/edu/year/2011/course/TDA357/HT2011/'" +
				",'Programming experience (in Java) is a prerequisite, as is basic mathematical maturity, and a course in data structures. Dispensations can only be permitted with the permission of the course instructor. '" +
				",'Databases are a ubiquitous feature of the information technology society, and most people have come in contact with them, either directly while using the world wide web, or indirectly through banks, the tax office, etc. A course on database systems is therefore essential to a well-rounded education in computer science. '" +
				", '12/12-10','V-Huset'" +
				",201967)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (223456,'Project Managment'" +
				",'https://www.student.chalmers.se/hp/hp?hp_id=8398' " +
				",'While not being a formal prerequisite, this course is designed under the assumption that participating students hold a major in software engineering, information technology, or computer science.' " +
				",'The course aims to impart a declarative knowledge as well as a reflective understanding of project work, i.e. the organisation leadership and control of project activity, in order to prepare the student for participating in and improving the student’s ability to improve the performance of project-related work.' " +
				", '15/12-10','A-Huset'" +
				",202049)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (323456,'Mobile Computing'" +
				",'http://www.ixdcth.se/courses/2011/ciu195/' " +
				",'To be eligible for the course the student must have a Bachelor degree of 180 credits. Special requirements are TDA545 Object-oriented programming, 7.5 credits, and at least one of the courses TDA289 Human - computer interaction, TDA491 Graphical interfaces, DAT215 Design and implementation of graphical interfaces or equivalent. ' " +
				",'After  the  course  the  students  should  be  able  to  take  an  idea  and  turn  it  into  a  finished application, or an app as it is usually called.  This  includes  both  the  design  of  the  graphical  interface  and  the  interaction,  as  well as  the  implementation  of  the  design. ' " +
				", '17/12-10','V-Huset'" +
				",201015)");
		db.execSQL("INSERT INTO course (id,name,homepage,prerequisites,purpose,date,location,scheduleId) " +
				"VALUES (423456,'Calculus'" +
				",'http://www.chalmers.se/math/SV/utbildning/grundutbildning-chalmers/mastersutbildning/engineering-mathematics/mve160' " +
				",'Basics in linear algebra, analysis in several variables, and programming' " +
				",'The student will be able to model some real world problems from science and technology, using mathematical tools from earlier courses as well as some new ideas and methods.'" +
				", '10/11-10','V-Huset'" +
				",201689)");
				
		db.execSQL("drop table if exists complete");
		db.execSQL("create table complete(s_id references student (id), c_id references course (id), grade VARCHAR(2), date VARCHAR(50), primary key (s_id, c_id))");
		db.execSQL("INSERT INTO complete (s_id,c_id,grade) VALUES (870312,123456,'4')");
		db.execSQL("INSERT INTO complete (s_id,c_id,grade) VALUES (870312,223456,'3')");
				
		db.execSQL("drop table if exists registerd");
		db.execSQL("create table registerd(s_id references student (id), c_id references course (id), primary key (s_id, c_id))");
		db.execSQL("INSERT INTO registerd (s_id,c_id) VALUES (870312,123456)");
		db.execSQL("INSERT INTO registerd (s_id,c_id) VALUES (870312,323456)");
				
		db.execSQL("drop table if exists examreg");
		db.execSQL("create table examreg(s_id references student (id), c_id references course (id), code INTEGER,  primary key (s_id, c_id))");
		
	}
	
	//Get information from database
	public Cursor getAllStudents() {
		return select("SELECT id, name, password FROM student");
	}
	public Cursor getStudent(int id) {
		return select("SELECT name, password FROM student WHERE id="+ id + "");
	}
	/***
	 * Gets users password
	 * @param name of student
	 * @return password, null is student doesn't exist
	 */
	public String getStudentPasswordByName(String name){
		String pwd = null;
		Cursor c = select("SELECT password FROM student WHERE name= '"+ name + "'");
		if(c.moveToFirst()){
			pwd = c.getString(0);
		}
		
		return pwd;
	}
	public int getStudentIdByName(String name){
		int id = -1;
		Cursor c = select("SELECT id FROM student WHERE name= '"+ name + "'");
		if(c.moveToFirst()){
			id = c.getInt(0);
		}
		return id;
	}
	public Cursor getAllCourses() {
		return select("SELECT id AS '_id', name, homepage, prerequisites, purpose, date, location FROM course");
	}
	public Cursor getStudentComplete(int id) {
		return select("SELECT c_id, grade, date FROM complete WHERE s_id="+ id + "");
	}
	public Cursor getStudentRegistered(int id) {
		//in SQL lite '||' concatenates two strings
		return select("SELECT r.c_id AS '_id', c.name AS 'name', c.date || ', ' || c.location AS 'info', scheduleId " +
						"FROM registerd r " +
						"LEFT JOIN course c " +
						"ON r.c_id = c.id " +
						"WHERE r.s_id="+ id + "");
	}
	public Cursor getStudentExams(int id) {
		return select("SELECT c_id, code FROM examreg WHERE s_id="+ id + "");
	}
	public Course getCourse(int id){
		Course course = new Course();
		
		//Get data from database
		Cursor c = select("SELECT id,name,homepage, prerequisites, purpose ,date,location FROM course WHERE id="+ id + "");
		c.moveToFirst();
		
		course.setId(c.getInt(0));
		course.setName(c.getString(1));
		course.setHomepage(c.getString(2));
		course.setPrerequisites(c.getString(3));
		course.setPurpose(c.getString(4));
		course.setDate(c.getString(5));
		course.setLocation(c.getString(6));
		
		return course;
	}
	public String getCourseName(int id){
		Cursor c = select("SELECT name FROM course WHERE id="+ id + "");
		c.moveToFirst();
		return c.getString(0); 
	}
	
	public int getCourseId(String name){
		Cursor c = select("SELECT id FROM course WHERE name='"+ name + "'");
		c.moveToFirst();
		return c.getInt(0); 
	}
	
	//Insert data to the database
	public void addStudent(int id, String name, String password) {
		exec("INSERT INTO student (id, name, password)  VALUES  ('" + id + "','" + name + "','" + password + "')");
	}
	public int studentRegisteredForExam(int s_id, int c_id){
		Cursor c = select("SELECT code FROM examreg WHERE s_id='" + s_id + "' AND c_id='" + c_id + "'");
		if(c.moveToFirst()){
			return c.getInt(0);
		}else{
			return -1;
		}
	}
	public void registerForExam(int s_id, int c_id, int code) {
		exec("INSERT INTO examreg (s_id, c_id, code)  VALUES  ('" + s_id + "','" + c_id + "','" + code + "')");
	}
	public void unregisterForExam(int s_id, int c_id){
		exec("DELETE FROM examreg WHERE s_id='" + s_id + "'" + " AND c_id='" + c_id + "'");
	}
	
	public void deleteRegistred(int s_id){
		exec("DELETE FROM registerd where s_id="+ s_id + "");
	}
	
	public void addRegisterdCourse(int s_id, int c_id){
		exec("INSERT INTO registerd (s_id, c_id) VALUES (" + s_id +","+ c_id + ")");
	}
	
	
	
}